/*
 * 
 */
package com.spartancoder.accommodation.models;

import com.spartancoder.accommodation.beans.StudentPreferenceBean;
import com.spartancoder.accommodation.db.Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

/**
 *
 * @author andrei
 */
public class Preferences {

	private static Logger log = Logger.getLogger(Preferences.class.getName());
	private static Database db = Database.getInstance();

	public static void create(StudentPreferenceBean spb) {
		try {
			Connection conn = db.getConnection();
			String query = "insert into `preferences`" +
					"(`session_id`, `student_id1`, `student_id2`)values" +
					"(?, ?,  ?)";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, spb.getSessionId());
			st.setInt(2, Math.min(spb.getStudentId1(), spb.getStudentId2()));
			st.setInt(3, Math.max(spb.getStudentId1(), spb.getStudentId2()));

			st.execute();

			st.close();
			conn.close();
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void remove(int preferenceId) {
		try {
			Connection conn = db.getConnection();
			String query = "delete from `preferences` where `preference_id`=?";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, preferenceId);

			st.execute();

			st.close();
			conn.close();

		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static List<StudentPreferenceBean> fetch(int studentId, int sessionId) {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `preferences` " +
					"where `student_id`=?, `session_id`=? ";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, studentId);
			st.setInt(2, sessionId);

			ResultSet rs = st.executeQuery();
			List<StudentPreferenceBean> ret = new LinkedList<StudentPreferenceBean>();
			while (rs.next()) {
				StudentPreferenceBean spb = new StudentPreferenceBean();
				spb.fill(rs);
				ret.add(spb);
			}

			rs.close();
			st.close();
			conn.close();

			return ret;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}
}
